package com.data.dao;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.jdbc.core.simple.ParameterizedBeanPropertyRowMapper;
import org.springframework.stereotype.Repository;

import com.base.dao.TemplateDaoImpl;
import com.data.entity.ChargeWeek;
@Repository("chargeWeekDao")
public class ChargeWeekDaoImpl extends TemplateDaoImpl<ChargeWeek, Integer>implements ChargeWeekDao{
	/**
	 * 根据年份和所属周查询电费周记录
	 * @param id  生产线或者设备id
	 * @param belongYear
	 * @param belongWeek
	 * @param chargeName
	 *  @param isLine
	 * @return
	 */
	@Override
	public ChargeWeek getChargeWeekByDate(int id, int belongYear,int belongWeek,String chargeName,String isLine) {
		StringBuilder sb=new StringBuilder("select * from chargeWeek where ");
		if(isLine.equals("Y")){
			sb.append(" lineId=:id ");
		}else{
			sb.append(" equId=:id ");
		}
		sb.append("  and belongYear=:belongYear and belongWeek=:belongWeek and chargeName=:chargeName");
		
//		String  sql="select * from chargeWeek where equId=:equId and belongYear=:belongYear and belongWeek=:belongWeek and chargeName=:chargeName";
		Map<String, Object> params=new HashMap<String, Object>();
		params.put("id", id);
		params.put("belongYear", belongYear);
		params.put("belongWeek", belongWeek);
		params.put("chargeName", chargeName);
		return jt.queryForObject(sb.toString(), params, 
				ParameterizedBeanPropertyRowMapper.newInstance(ChargeWeek.class));
	}
	
	/**
	 * 获取时间区间内的所有chargeName
	 * @param belongYear
	 * @param startWeek
	 * @param endWeek
	 * @return
	 */
	@Override
	public List<ChargeWeek> getDistinctChargeName( int belongYear,
			int startWeek, int endWeek) {
		StringBuffer sql=new StringBuffer("select distinct(chargeName) from chargeWeek  where belongYear=:belongYear and belongWeek>=:startWeek and belongWeek<=:endWeek");
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("belongYear", belongYear);
		params.put("startWeek", startWeek);
		params.put("endWeek", endWeek);
		return jt.query(sql.toString(),params,ParameterizedBeanPropertyRowMapper.newInstance(ChargeWeek.class));
	}

	/**
	 * 根据类型，时间，equid找到累计的电度电费
	 * @param equId
	 * @param belongYear
	 * @param startWeek
	 * @param endWeek
	 * @param chargeName
	 * @return
	 */
	@Override
	public ChargeWeek getDurationByChargeNameAndDate(int id,int belongYear, int startWeek, int endWeek, String chargeName,String isLine) {
		Map<String, Object> params=new HashMap<String, Object>();
		StringBuilder sb=new StringBuilder("select COALESCE(SUM(relativeComp),0) relativeComp,COALESCE(SUM(charge),0) charge  from chargeWeek where ");
		if(isLine.equals("Y")){
			sb.append(" lineId=:id ");
		}else{
			sb.append(" equId=:id ");
		}
		if(chargeName!=null&&!chargeName.equals("")){
			sb.append(" and chargeName=:chargeName");
			params.put("chargeName", chargeName);
		}
		sb.append(" and belongYear=:belongYear and belongWeek>=:startWeek and belongWeek<=:endWeek ");
//		String sql="select COALESCE(SUM(relativeComp),0) relativeComp,COALESCE(SUM(charge),0) charge from chargeWeek  where equId=:equId and belongYear=:belongYear and belongWeek>=:startWeek and belongWeek<=:endWeek and chargeName=:chargeName ";
		params.put("id", id);
		params.put("belongYear", belongYear);
		params.put("startWeek", startWeek);
		params.put("endWeek", endWeek);
		return jt.queryForObject(sb.toString(), params, ParameterizedBeanPropertyRowMapper.newInstance(ChargeWeek.class));
	
	}

	/**
	 * 根据周区间找到的各自使用电费
	 * @param equId
	 * @param belongYear
	 * @param startWeek
	 * @param endWeek
	 * @param chargeName
	 * @return
	 */
	@Override
	public List<ChargeWeek> getChargeByDates(int id,int belongYear,int startWeek, int endWeek,String chargeName,String isLine) {
		Map<String, Object> params=new HashMap<String, Object>();
		StringBuilder sb=new StringBuilder("select  *  from  chargeWeek  where ");
//		StringBuffer sql=new StringBuffer("select  *  from  chargeWeek  where equId=:equId and belongYear=:belongYear and belongWeek>=:startWeek and belongWeek<=:endWeek and chargeName=:chargeName ");
		if(isLine.equals("Y")){
			sb.append(" lineId=:id ");
		}else{
			sb.append(" equId=:id ");
		}
		if(chargeName!=null&&!chargeName.equals("")){
			sb.append(" and chargeName=:chargeName");
			params.put("chargeName", chargeName);
		}
		sb.append(" and belongYear=:belongYear and belongWeek>=:startWeek and belongWeek<=:endWeek ");
		params.put("id", id);
		params.put("belongYear", belongYear);
		params.put("startWeek", startWeek);
		params.put("endWeek", endWeek);
		return jt.query(sb.toString(),params,ParameterizedBeanPropertyRowMapper.newInstance(ChargeWeek.class));
	}

	/**
	 * 根据belongWeek找到该周使用的总电度
	 * @param equId
	 * @param belongYear
	 * @param belongWeek
	 * @return
	 */
	@Override
	public ChargeWeek getCompByDate(int id,int belongYear, int belongWeek,String isLine) {
		StringBuilder sb=new StringBuilder("select COALESCE(SUM(relativeComp),0) relativeComp, COALESCE(SUM(charge),0) charge  from chargeWeek  where ");
		if(isLine.equals("Y")){
			sb.append(" lineId=:id ");
		}else{
			sb.append(" equId=:id ");
		}
		sb.append(" and belongYear=:belongYear and belongWeek=:belongWeek ");
//		String sql="select COALESCE(SUM(relativeComp),0) relativeComp from chargeWeek  where equId=:equId and belongYear=:belongYear and belongWeek=:belongWeek ";
		Map<String, Object> params=new HashMap<String, Object>();
		params.put("id", id);
		params.put("belongYear", belongYear);
		params.put("belongWeek", belongWeek);
		return jt.queryForObject(sb.toString(), params, 
				ParameterizedBeanPropertyRowMapper.newInstance(ChargeWeek.class));
	}
}
